#-*-coding:utf-8-*-
import pyodbc
import os
def SelectInfo(hcon,hcur):
	hcur.execute('select * from PassMapT')
	ptitle=('ID','Item','Pwd','other')
	print(ptitle)
	result=hcur.fetchall()
	for item in result:
		print(item)
	print('')

def AddInfo(hcon,hcur):
	id=int(input('please input ID: '))
	item=str(input('please input Item: '))
	pwd=str(input('please input Tel 1: '))
	other=str(input('please input Other: '))
	sql="insert into PassMapT(id,item,pwd,other) values(?,?,?,?)"
	try:
		hcur.execute(sql,(id,item,pwd,other))
		hcon.commit()
	except:
		hcon.rollback()

def DeleteInfo(hcon,hcur):
	SelectInfo(hcon,hcur)
	did=int(input('please input id of delete: '))
	sql="delete from PassMapT where id=?"
	try:
		hcur.execute(sql,(did,))
		hcon.commit()
	except:
		hcon.rollback()

def UpdateInfo(hcon,hcur):
	SelectInfo(hcon,hcur)
	did=int(input('please input id of update: '))
	
	sqlitem="update PassMapT set item=? where id=?"
	item=str(input('please input Item: '))
	try:
		hcur.execute(sqlitem,(item,did))
		hcon.commit()
	except:
		hcon.rollback()
	
	sqlpwd="update PassMapT set pwd=? where id=?"
	pwd=str(input('please input Pwd: '))
	try:
		hcur.execute(sqlpwd,(pwd,did))
		hcon.commit()
	except:
		hcon.rollback()
	
	sqlother="update PassMapT set other=? where id=?"
	other=str(input('please input other: '))
	try:
		hcur.execute(sqlother,(other,did))
		hcon.commit()
	except:
		hcon.rollback()
		
def Meau():
	print('1.diaplay')
	print('2.add')
	print('3.update')
	print('4.delete')
	print('5.cls')
	print('0.exit')
	sel=9
	while(sel>5 or sel<0):
		sel=int(input('please choice: '))
	return sel

def main():
	hcon = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=127.0.0.1;DATABASE=PasswordMap;UID=sa;PWD=ltb12315')
	hcur=hcon.cursor()
	
	while(True):
		sel=Meau()
		if(sel==1):
			SelectInfo(hcon,hcur)
		elif(sel==2):
			AddInfo(hcon,hcur)
		elif(sel==3):
			UpdateInfo(hcon,hcur)
		elif(sel==4):
			DeleteInfo(hcon,hcur)
		elif(sel==5):
			os.system('cls')
		else:
			break
	hcur.close()
	hcon.close()

if __name__=='__main__':
	main()